package com.scalar.forecast.salesPersonSetup.dao;

import com.scalar.forecast.salesPersonSetup.forms.SalesPersonSetupForm;
import com.scalar.forecast.utils.ForecastSqlQueryMappings;
import com.scalar.forecast.salesPersonSetup.SalesPerson;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.ScalarException;

import java.sql.*;
import java.util.ArrayList;




/**
 *
 * Created by: Gandhi
 * Date: Feb 8, 2005
 * Time: 6:30:25 PM
 *
 */
public class SalesPersonSetupDAO {


    public static ArrayList getSalesPersonList() throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        ArrayList salesPersonList = new ArrayList();

        try {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement(ForecastSqlQueryMappings.get("forecast.salesPerson.loadSalesPersons"));
            //pstmt = conn.prepareStatement("select distinct SalesPerson_Id,SalesPerson_Name from sales_persons where Deactivated <> 1 order by SalesPerson_Name");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                String salesPerson_Id = rs.getString("SalesPerson_Id");
                String salesPerson_Name = rs.getString("SalesPerson_Name");

                salesPersonList.add(new org.apache.struts.util.LabelValueBean(salesPerson_Name, salesPerson_Id));
            }
        } catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException("Could not get Sales Persons from database", sqlex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException excp) {
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException excp) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException excp) {
                }
            }
        }

        return salesPersonList;
    }


    public static ArrayList getUserList() throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        ArrayList userList = new ArrayList();

        try {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement(ForecastSqlQueryMappings.get("forecast.salesPerson.loadAllExistingUsers"));
            //pstmt = conn.prepareStatement("SELECT distinct User_ID from User_Table where User_Table.Disabled = 1 and expires_on >= convert(varchar, CURRENT_TIMESTAMP, 101) order by User_ID");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                String userId = rs.getString("User_ID");

                userList.add(new org.apache.struts.util.LabelValueBean(userId, userId));
            }
        } catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException("Could not get Users from database", sqlex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException excp) {
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException excp) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException excp) {
                }
            }
        }

        return userList;
    }


    public static ArrayList getLevelList() throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        ArrayList levelList = new ArrayList();

        try {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement(ForecastSqlQueryMappings.get("forecast.salesPerson.loadAllLevels"));
            //pstmt = conn.prepareStatement("select distinct LevelOrder,LevelName from FCLEVEL where LevelSelect = 0 order by LevelOrder");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                String levelOrder = rs.getString("LevelOrder");
                String levelName = rs.getString("LevelName");

                levelList.add(new org.apache.struts.util.LabelValueBean(levelName, levelOrder));
            }
        } catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException("Could not get Levels from database", sqlex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException excp) {
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException excp) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException excp) {
                }
            }
        }

        return levelList;
    }

    /**
	 * This method is used to get the manager list.<br>
	 * It will not include the salesperson with id <slaesPersonId> in managerList.
	 * @param levelOrder
	 * level order for the managerlist
	 * @param salesPersonId
	 * salesPersonId - this salesperson is not included in managerList.
	 * @return
	 * @throws ScalarException
	 */
    public static ArrayList getManagerList( int levelOrder, int salesPersonId ) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        ArrayList mgrList = new ArrayList();

        try {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement(ForecastSqlQueryMappings.get("forecast.salesPerson.loadAllManagers"));
            //pstmt = conn.prepareStatement("select SPERSONID as mgrId,SalesPerson_Name as mgrName from FCSPERSONREL,Sales_Persons where levelOrder = ? and SPERSONID = SalesPerson_Id and SPERSONID != ?");
            pstmt.setInt(1, levelOrder);
			pstmt.setInt ( 2, salesPersonId );
            rs = pstmt.executeQuery();
            while (rs.next()) {
                String mgrId = rs.getString("mgrId");
                String mgrName = rs.getString("mgrName");

                mgrList.add(new org.apache.struts.util.LabelValueBean(mgrName, mgrId));
            }
        } catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException("Could not get Managers from database", sqlex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException excp) {
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException excp) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException excp) {
                }
            }
        }

        return mgrList;
    }


    public static int getLevelOrder(int salesPersonId) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int levelOrder = -1;

        try {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement(ForecastSqlQueryMappings.get("forecast.salesPerson.getLevelOrder"));
            //pstmt = conn.prepareStatement("Select LEVELORDER from FCSPERSONREL where SPERSONID = ?");
            pstmt.setInt(1, salesPersonId);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                levelOrder = rs.getInt("LEVELORDER");
            }
        } catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException("Could not get Level Order from database", sqlex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException excp) {
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException excp) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException excp) {
                }
            }
        }

        return levelOrder;
    }


    public static boolean isSalesPersonIdExist(DBTransaction dbTransaction, int salesPersonId) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;

        try {
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            } else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement(ForecastSqlQueryMappings.get("forecast.salesPerson.getSalesPersonId"));
            //pstmt = conn.prepareStatement("select * from FCSPERSONREL WHERE SPERSONID = ?");
            pstmt.setInt(1, salesPersonId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                success = true;
            } else {
                success = false;
            }

        } catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not get Sales Person Id From database", sqle);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ignore) {
                }
            }
        }

        return success;
    }


    public static boolean isUserIdExists(DBTransaction dbTransaction, int salesPersonId, String userId) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;

        try {
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            } else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement(ForecastSqlQueryMappings.get("forecast.salesPerson.getUserId"));
            pstmt.setInt(1, salesPersonId);
            pstmt.setString(2, userId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                success = true;
            } else {
                success = false;
            }

        } catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not get User Id From database", sqle);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ignore) {
                }
            }
        }

        return success;
    }


    public static void insertSalesPersonInfo(DBTransaction dbTransaction, SalesPersonSetupForm spsf, boolean isInsert) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;

        try {

            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            } else {
                conn = dbTransaction.getConnection();
            }

            if (isInsert == false) {

                pstmt = conn.prepareStatement(ForecastSqlQueryMappings.get("forecast.salesPerson.updateSalesPerson"));
                //pstmt = conn.prepareStatement("Update FCSPERSONREL set USERID=?,LEVELORDER=?,MGRID=? where SPERSONID=?");
                pstmt.setString(1, spsf.getExistingUserId());
                pstmt.setInt(2, spsf.getForecastLevelOrder());
                if (spsf.getManagerId() > 0) {
                    pstmt.setInt(3, spsf.getManagerId());
                } else {
                    pstmt.setString(3, null);
                }
                pstmt.setInt(4, spsf.getExistingSalesPersonId());

            } else {
                pstmt = conn.prepareStatement(ForecastSqlQueryMappings.get("forecast.salesPerson.insertSalesPerson"));
                //pstmt = conn.prepareStatement("Insert into FCSPERSONREL values(?,?,?,?)");
                pstmt.setInt(1, spsf.getExistingSalesPersonId());
                pstmt.setString(2, spsf.getExistingUserId());
                pstmt.setInt(3, spsf.getForecastLevelOrder());
                if (spsf.getManagerId() > 0) {
                    pstmt.setInt(4, spsf.getManagerId());
                } else {
                    pstmt.setString(4, null);
                }
            }
            pstmt.executeUpdate();
            success = true;
        } catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not insert Sales Person Info into database", sqle);
        } finally {
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        } else {
                            conn.rollback();
                        }
                    }
                } catch (SQLException ignore) {
                }
            }
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ignore) {
                }
            }
        }
    }

    public static void getSalesPerson(DBTransaction dbTransaction, SalesPersonSetupForm spsf, int salesPersonId) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;


        try {
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            } else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement(ForecastSqlQueryMappings.get("forecast.salesPerson.getSalesPersonInfo"));
            //pstmt = conn.prepareStatement("select SPERSONID,USERID,LEVELORDER,MGRID from FCSPERSONREL WHERE SPERSONID = ?");
            pstmt.setInt(1, salesPersonId);
            rs = pstmt.executeQuery();
            int sPersonId       = -1;
            int levelOrder          = -1;
            int mgrId               = -1;
            ArrayList userIds       = new ArrayList();
            while (rs.next())
            {
                sPersonId = rs.getInt ( "SPERSONID" );
                userIds.add ( rs.getString ( "USERID" ) );
                levelOrder = rs.getInt ( "LEVELORDER" );
                mgrId = rs.getInt ( "MGRID" );

                spsf.setExistingSalesPersonId( sPersonId );
                spsf.setForecastLevelOrder( levelOrder );
                spsf.setManagerId( mgrId );
            }

            String existingUserIds[];
            Object obj[] = userIds.toArray();
            int len = obj.length;
            existingUserIds = new String[ len ];
            for ( int i =0 ; i < len; i++ )
            {
                existingUserIds[i] = ( String )obj[i];
            }
            spsf.setExistingUserIds( existingUserIds );
            success = true;
        } catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not get Sales Person Info from database", sqle);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ignore) {
                }
            }
        }
    }
    /*
     *  Loads all the Sales Person setup details
     */
    public static ArrayList loadSalesPersonsList( DBTransaction dbTransaction ) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;

        ArrayList salesPersonsList =  new ArrayList();

        try {
            if ( dbTransaction == null ) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement(ForecastSqlQueryMappings.get("forecast.salesPerson.loadAllSalesPersonInfo"));

            rs = pstmt.executeQuery();
            while( rs.next() ){
                salesPersonsList.add( new SalesPerson( rs.getString("SALESPERSON_NAME"), rs.getString("LEVEL_NAME"), rs.getString("MANAGER"), rs.getString("USER_IDS") ) );
            }
            success = true;

        } catch( Exception e ) {
            e.printStackTrace();
            throw new ScalarException("Could not load Sales Persons from database");
        }
        finally
        {
            if (dbTransaction == null)
            {
                 try
                 {
                     if (conn != null)
                     {
                         if (success == true)
                             conn.commit();
                         else
                             conn.rollback();
                     }
                 }
                 catch (SQLException ignore) {}
             }
             if ( rs != null )
             {
                 try { rs.close(); } catch ( SQLException ignore ) { }
             }
             if (pstmt != null)
             {
                 try { pstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }
        return salesPersonsList;
    }

	//Added by Sujan on 6th may 2005
	/**
	 * checks whethere there are subordinates for this salesPersonId.
	 * @param dbTransaction
	 * @param salesPersonId
	 * @return
	 * true - if there are subordinates.<br>
	 * false - if no subordinates present OR if the salesPersonId is not present in the table.
	 * @throws ScalarException
	 */
    public static boolean hasSubordinatesForTheSalesPerson ( DBTransaction dbTransaction, int salesPersonId ) throws ScalarException
	{
		Connection conn 		= null;
		PreparedStatement pstmt = null;
		ResultSet rset 			= null;
		boolean hasSubordinates = false;
		boolean success 		= false;
		try
		{
			if ( dbTransaction == null )
			{
				conn = DataSource.getConnection ();
			}
			else
			{
				conn = dbTransaction.getConnection ();
			}
			pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.salesPerson.getSubordinateCount" ) );
			pstmt.setInt ( 1, salesPersonId );
			rset = pstmt.executeQuery();
			if ( rset.next() )
			{
				if ( rset.getInt ( 1 ) > 0 )
				{
					hasSubordinates = true;
				}
			}
			success = true;
		}
		catch ( Exception e )
		{
			System.out.println ( "----------Exception While Checking For Subordinates For The SalesPerson------" );
			e.printStackTrace();
			throw new ScalarException ( "Could Not check subordinates for the salesperson", e );
		}
		finally
		{
			if ( dbTransaction == null )
			{
				if ( conn != null )
				{
					if ( success )
					{
						try
						{
							conn.commit();
						}
						catch ( SQLException e )
						{
							System.out.println ( "---Could Not Commit---" );
							e.printStackTrace();
							throw new ScalarException ( "Could Not Commit", e );
						}
					}
					else
					{
						try
						{
							conn.rollback();
						}
						catch ( SQLException e )
						{
							System.out.println ( "---Could Not Rollback---" );
							e.printStackTrace();
							throw new ScalarException ( "Could Not Rollback", e );
						}
					}
				}
			}
			if ( rset != null )
			{
				try { rset.close(); } catch ( SQLException ignore ) { }
			}
			if ( pstmt != null )
			{
				try { pstmt.close(); } catch ( SQLException ignore ) { }
			}
			if ( conn != null )
			{
				try { conn.close(); } catch ( SQLException ignore ) { }
			}
		}
		return hasSubordinates;
	}

    /**
     * Method to get SalesPerson Name
     *
     * @param dbTransaction
     * @param salesPersonID
     * @return
     * @throws ScalarException
     */

    public static String getSalesPersonName( DBTransaction dbTransaction, int salesPersonID ) throws ScalarException
    {
        Connection        conn  = null;
        PreparedStatement pstmt = null;
        ResultSet         rset  = null;

        String salesPersonName  = null;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareCall( "SELECT SALESPERSON_NAME FROM SALES_PERSONS WHERE SALESPERSON_ID = ?" );
            pstmt.setInt( 1, salesPersonID );
            rset = pstmt.executeQuery();
            if( rset.next() )
            {
                salesPersonName = rset.getString( "SALESPERSON_NAME" );
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("could not retrive customer number", sqle);
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return salesPersonName;
    }

    public static void saveSalesPersonInfo ( DBTransaction dbTransaction, SalesPersonSetupForm salesPersonSetupForm ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;

        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection ();
            }

            //DELETE ALL RECORDS OF THE SALES PERSON
            pstmt = conn.prepareStatement ( "DELETE FROM FCSPERSONREL WHERE SPERSONID = ?" );
            pstmt.setInt ( 1, salesPersonSetupForm.getExistingSalesPersonId() );

            pstmt.executeUpdate();

            //NOW INSERT RECORDS FOR THE SALES PERSON
            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get("forecast.salesPerson.insertSalesPerson") );

            String userIds[] = salesPersonSetupForm.getExistingUserIds();
            int userIdsLen  = userIds.length;
            for ( int i = 0 ; i < userIdsLen; i++ )
            {
                pstmt.setInt    ( 1, salesPersonSetupForm.getExistingSalesPersonId() );
                pstmt.setString ( 2, userIds[i] );
                pstmt.setInt    ( 3, salesPersonSetupForm.getForecastLevelOrder() );
                if (salesPersonSetupForm.getManagerId() > 0)
                {
                    pstmt.setInt( 4, salesPersonSetupForm.getManagerId() );
                }
                else
                {
                    pstmt.setString(4, null);
                }

                pstmt.addBatch();
            }

            if ( userIdsLen > 0 )
            {
                pstmt.executeBatch();
            }
            success = true;
        }
        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not save the Sales Person setup" , e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch( Exception e )
                    {
                        System.out.println ( "=======exception while committ/rollback=========" );
                        e.printStackTrace();
                    }
                }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch( SQLException ignore ) { }
            }
        }
    }
}
